#!/usr/bin/env python3
"""
数据库工具函数
"""
import os
import sqlite3
import datetime
from pathlib import Path
from typing import Optional, Tuple

def get_backup_dir() -> str:
    """获取备份目录"""
    print(f"当前工作目录: {os.getcwd()}")
    
    # 方法1: 尝试使用应用根目录下的backups目录
    try:
        app_root = Path(__file__).resolve().parent.parent.parent
        backup_dir = app_root / "guzian_app" / "backups"
        print(f"尝试备份目录路径1: {backup_dir}")
        
        # 确保目录存在并设置正确的权限
        os.makedirs(backup_dir, exist_ok=True)
        # 设置目录权限为可写
        try:
            os.chmod(backup_dir, 0o755)
        except Exception as e:
            print(f"警告: 无法设置备份目录权限: {str(e)}")
        
        # 验证目录是否可写
        if os.access(str(backup_dir), os.W_OK):
            print(f"备份目录可写: {backup_dir}")
            return str(backup_dir)
        else:
            print(f"警告: 备份目录1不可写: {backup_dir}")
    except Exception as e:
        print(f"获取备份目录1出错: {str(e)}")
    
    # 方法2: 尝试使用当前工作目录下的路径（Docker兼容方案）
    try:
        # 检查是否存在挂载的卷
        if os.path.exists("/app/guzian_app/backups"):
            backup_dir = Path("/app/guzian_app/backups")
            print(f"尝试备份目录路径2: {backup_dir}")
            
            # 确保目录存在并设置正确的权限
            os.makedirs(backup_dir, exist_ok=True)
            # 设置目录权限为可写
            try:
                os.chmod(backup_dir, 0o755)
            except Exception as e:
                print(f"警告: 无法设置备份目录2权限: {str(e)}")
            
            # 验证目录是否可写
            if os.access(str(backup_dir), os.W_OK):
                print(f"备份目录2可写: {backup_dir}")
                return str(backup_dir)
            else:
                print(f"警告: 备份目录2不可写: {backup_dir}")
    except Exception as e:
        print(f"获取备份目录2出错: {str(e)}")
    
    # 方法3: 尝试使用相对路径（最后的备选方案）
    try:
        backup_dir = Path("guzian_app/backups")
        print(f"尝试备份目录路径3: {backup_dir}")
        
        # 确保目录存在并设置正确的权限
        os.makedirs(backup_dir, exist_ok=True)
        # 设置目录权限为可写
        try:
            os.chmod(backup_dir, 0o755)
        except Exception as e:
            print(f"警告: 无法设置备份目录3权限: {str(e)}")
        
        # 验证目录是否可写
        if os.access(str(backup_dir), os.W_OK):
            print(f"备份目录3可写: {backup_dir}")
            return str(backup_dir)
        else:
            print(f"警告: 备份目录3不可写: {backup_dir}")
    except Exception as e:
        print(f"获取备份目录3出错: {str(e)}")
    
    # 如果所有方法都失败，返回默认路径
    default_backup_dir = Path("backups")
    print(f"使用默认备份目录: {default_backup_dir}")
    os.makedirs(default_backup_dir, exist_ok=True)
    return str(default_backup_dir)

def backup_database(db_path: str) -> Tuple[bool, str]:
    """
    备份数据库，确保备份的完整性和正确的编码格式
    
    Args:
        db_path: 数据库文件路径
    
    Returns:
        Tuple[bool, str]: (成功状态, 消息/备份文件路径)
    """
    print(f"开始数据库备份，源路径: {db_path}")
    
    try:
        # 验证数据库文件是否存在
        if not os.path.exists(db_path):
            error_msg = f"数据库文件不存在: {db_path}"
            print(error_msg)
            return False, error_msg
        
        # 验证数据库文件是否可读
        if not os.access(db_path, os.R_OK):
            error_msg = f"无法读取数据库文件，权限不足: {db_path}"
            print(error_msg)
            return False, error_msg
        
        # 获取数据库文件大小
        db_size = os.path.getsize(db_path)
        print(f"数据库文件大小: {db_size} 字节")
        
        # 创建备份目录
        backup_dir = get_backup_dir()
        print(f"备份目录: {backup_dir}")
        
        # 验证备份目录是否可写
        if not os.access(backup_dir, os.W_OK):
            error_msg = f"无法写入备份目录，权限不足: {backup_dir}"
            print(error_msg)
            return False, error_msg
        
        # 生成备份文件名
        timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_filename = f"guzian_backup_{timestamp}.sql"
        backup_path = os.path.join(backup_dir, backup_filename)
        print(f"备份文件路径: {backup_path}")
        
        # 方法1: 首先尝试使用Python API进行完整备份（推荐方式，确保编码正确）
        print("尝试使用Python API进行备份...")
        try:
            # 使用with语句确保连接正确关闭
            with sqlite3.connect(db_path) as conn:
                # 启用外键检查以确保数据完整性
                conn.execute("PRAGMA foreign_keys = ON")
                
                # 使用二进制模式写入，确保所有字符都能正确保存
                with open(backup_path, 'wb') as f:
                    print(f"开始写入备份文件: {backup_path}")
                    line_count = 0
                    for line in conn.iterdump():
                        # 先将每行编码为UTF-8，确保特殊字符正确处理
                        f.write((line + '\n').encode('utf-8'))
                        line_count += 1
                    print(f"备份文件写入完成，共写入 {line_count} 行")
                
                # 验证备份文件的大小和完整性
                if os.path.exists(backup_path):
                    backup_size = os.path.getsize(backup_path)
                    print(f"Python API备份成功，备份文件大小: {backup_size} 字节")
                    
                    # 验证备份文件是否合理（至少包含一些数据）
                    if backup_size > 0 and line_count > 5:
                        return True, backup_path
                    else:
                        # 如果备份文件存在但大小异常，删除它
                        os.remove(backup_path)
                        error_msg = f"备份文件大小异常: {backup_size} 字节, 行数: {line_count}"
                        print(error_msg)
                        raise Exception(error_msg)
                else:
                    raise Exception("备份文件生成失败")
            
        except Exception as python_api_error:
            print(f"Python API备份失败: {str(python_api_error)}")
            # 方法2: 如果Python API失败，尝试使用sqlite3命令行工具（二进制模式）
            print("尝试使用命令行sqlite3工具进行备份...")
            try:
                import subprocess
                
                # 检查sqlite3命令是否可用
                try:
                    subprocess.run(["sqlite3", "--version"], check=True, capture_output=True)
                    print("sqlite3命令可用")
                except (subprocess.SubprocessError, FileNotFoundError):
                    raise Exception("sqlite3命令不可用，请确保已安装")
                
                # 使用二进制模式运行命令，避免编码问题
                print(f"执行命令: sqlite3 {db_path} .dump")
                result = subprocess.run(
                    ["sqlite3", db_path, ".dump"],
                    check=True,
                    capture_output=True
                )
                
                # 检查命令输出是否为空
                if not result.stdout:
                    raise Exception("sqlite3命令输出为空")
                
                # 以二进制模式写入备份文件
                with open(backup_path, 'wb') as f:
                    f.write(result.stdout)
                
                # 验证备份是否成功
                if os.path.exists(backup_path):
                    backup_size = os.path.getsize(backup_path)
                    print(f"命令行备份成功，备份文件大小: {backup_size} 字节")
                    
                    # 验证备份文件大小是否合理
                    if backup_size > 0:
                        return True, backup_path
                    else:
                        # 如果备份文件存在但大小为0，删除它
                        os.remove(backup_path)
                        error_msg = "命令行备份生成的文件为空"
                        print(error_msg)
                        raise Exception(error_msg)
                else:
                    raise Exception("命令行备份文件生成失败")
                    
            except subprocess.CalledProcessError as e:
                error_msg = e.stderr.decode('utf-8', errors='replace') if e.stderr else str(e)
                print(f"命令行备份失败: {error_msg}")
                return False, f"命令行备份失败: {error_msg}"
            except Exception as cmd_error:
                print(f"命令行备份异常: {str(cmd_error)}")
                # 整合错误信息
                return False, f"所有备份方法失败: Python API错误({str(python_api_error)}), 命令行错误({str(cmd_error)})"
                
    except Exception as e:
        error_msg = f"备份失败: {str(e)}"
        print(error_msg)
        import traceback
        traceback.print_exc()  # 打印完整的异常堆栈
        return False, error_msg

def get_latest_backup() -> Optional[str]:
    """
    获取最新的备份文件路径
    
    Returns:
        Optional[str]: 备份文件路径，如果没有备份则返回None
    """
    backup_dir = get_backup_dir()
    try:
        backups = [f for f in os.listdir(backup_dir) if f.startswith("guzian_backup_")]
        if not backups:
            return None
        
        # 按文件名排序（文件名包含时间戳）
        backups.sort(reverse=True)
        return os.path.join(backup_dir, backups[0])
    except Exception:
        return None

def get_all_backups() -> list:
    """
    获取所有备份文件信息列表
    
    Returns:
        list: 包含备份文件信息的字典列表
    """
    backup_dir = get_backup_dir()
    backups = []
    try:
        # 获取所有备份文件
        backup_files = [f for f in os.listdir(backup_dir) if f.startswith("guzian_backup_")]
        
        for backup_file in backup_files:
            # 提取时间戳
            timestamp = backup_file.replace("guzian_backup_", "").replace(".sql", "")
            
            # 获取文件大小
            file_path = os.path.join(backup_dir, backup_file)
            file_size = os.path.getsize(file_path) if os.path.exists(file_path) else 0
            
            # 格式化文件大小
            if file_size < 1024:
                size_str = f"{file_size} B"
            elif file_size < 1024 * 1024:
                size_str = f"{file_size / 1024:.2f} KB"
            else:
                size_str = f"{file_size / (1024 * 1024):.2f} MB"
            
            # 获取文件修改时间
            file_mtime = os.path.getmtime(file_path) if os.path.exists(file_path) else 0
            modified_time = datetime.datetime.fromtimestamp(file_mtime).strftime("%Y-%m-%d %H:%M:%S")
            
            backups.append({
                "name": backup_file,
                "timestamp": timestamp,
                "size": size_str,
                "size_bytes": file_size,
                "modified_time": modified_time,
                "path": file_path
            })
        
        # 按修改时间降序排序
        backups.sort(key=lambda x: x["size_bytes"], reverse=True)
        
        return backups
    except Exception:
        return []

def delete_backup(backup_filename: str) -> bool:
    """
    删除指定的备份文件
    
    Args:
        backup_filename: 备份文件名
    
    Returns:
        bool: 删除是否成功
    """
    try:
        # 验证文件名格式
        if not backup_filename.startswith("guzian_backup_") or not backup_filename.endswith(".sql"):
            return False
        
        backup_dir = get_backup_dir()
        backup_path = os.path.join(backup_dir, backup_filename)
        
        # 检查文件是否存在
        if os.path.exists(backup_path):
            os.remove(backup_path)
            return True
        
        return False
    except Exception:
        return False